package com.jr.dao.impl;

import com.jr.dao.SupplierblacklistDao;
import com.jr.pojo.Enterprise;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;
import com.jr.vo.BlackEnter;
import com.jr.vo.EnterVo;
import com.jr.vo.WhiteEnter;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;

public class ISupplierblacklistDaoImpl implements SupplierblacklistDao {
    private DBHelper db = new DBHelper();

    /* *//**
     * 分页查询供应商白名单数据
     //查询参数实体
     供应商白名单实体列表
     */
    @Override
    public ArrayList<BlackEnter> selectSupplieBlackList(BlackEnter be, PageHelper ph, String myentername,String scale) {
        StringBuilder sb = new StringBuilder("SELECT\n" +
                "    sw.supplierid                 AS id,\n" +
                "    supplier_e.entername  AS entername,\n" +
                "    u.username            AS username,\n" +
                "    u.phone               AS phone,\n" +
                "    supplier_e.email      AS email,\n" +
                "    sw.reason      AS reason,\n" +
                "    sw.updatedate         AS updatedate\n" +
                "FROM supplierblacklist sw\n" +
                "JOIN enterprise e          ON sw.enterid   = e.id\n" +
                "JOIN enterprise supplier_e ON sw.supplierid = supplier_e.id\n" +
                "JOIN users u               ON u.entername = supplier_e.entername\n" +
                "WHERE e.entername = ?");
        LinkedList list = new LinkedList();
        list.add(myentername);
        if (be != null) {
            /*supplier_e.scale*/
            if (!StringUtils.isNullOrEmpty(be.getEntername())) {
                sb.append(" AND supplier_e.entername LIKE ?");
                list.add(be.getEntername());
            }
            /*我通过 传参数给 但是 */
            if(scale!=null && !scale.equals("")){
                sb.append(" AND supplier_e.scale like ?  ");
                list.add(scale);
            }

        }
        System.out.println(list + "backdao层集合");
        // 添加分页
        sb.append("LIMIT ?, ?");
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        if (index < 0) {
            index = 0;
        }
        list.add(index);
        list.add(ph.getShowNumber());
        list.get(0);

        System.out.println(ph.getListIndex() + "backdao层集合。listindex");
        System.out.println(ph + "backdao层ph对象");
        // 如果需要返回包含企业信息的对象，可以创建对应的VO类
        return db.dtQuery(sb.toString(), BlackEnter.class, list.toArray());
    }

    /**
     * 统计供应商白名单记录数量
     //white 查询参数实体
     记录总数
     */
    @Override
    public int selectSupplierBlackListCount(BlackEnter be, String nowentername,String scale) {
        /*当前账户所在企业名称*/
        StringBuilder sb = new StringBuilder("SELECT\n" +
                "    COUNT(*)\n" +
                "FROM supplierblacklist sw\n" +
                "JOIN enterprise e          ON sw.enterid   = e.id\n" +
                "JOIN enterprise supplier_e ON sw.supplierid = supplier_e.id\n" +
                "JOIN users u               ON u.entername = supplier_e.entername\n" +
                "WHERE e.entername = ?");
        /**/
        LinkedList list = new LinkedList();
        list.add(nowentername);  // 首先添加nowentername参数
        if(scale!=null && !scale.equals("")){
            sb.append(" AND supplier_e.scale like ?  ");
            list.add("%"+scale+"%");
        }
        if (be.getEntername() != null && !be.getEntername().equals("")) {
            sb.append(" AND supplier_e.entername LIKE ?");  // 修改为供应商名称搜索
            list.add("%" + be.getEntername() + "%");
        }
        Connection con = db.getCon();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sb.toString());
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    ps.setObject(i + 1, list.get(i));
                }
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, rs);
        }
        return 0;
    }

    /*查询可添加至供应商至黑名单 存入map集合*/
    @Override
    public Map<Integer, String> selectBlackEnterprise(String entername) {

        String sql =  "SELECT e.entername, e.id\n" +
                "FROM enterprise e\n" +
                "WHERE e.entername != ? \n" +
                "  AND e.id NOT IN ( \n" +
                "    SELECT s.supplierid \n" +
                "    FROM supplierblacklist s \n" +
                "    WHERE s.enterid = ( \n" +
                "      SELECT e2.id\n" +
                "      FROM enterprise e2 \n" +
                "      WHERE e2.entername = ? \n" +
                "    )\n" +
                "  )";
        ArrayList<EnterVo> enterpriseList = db.dtQuery(sql, EnterVo.class,  entername,entername);
        Map<Integer, String> enterpriseMap = new HashMap<>();
        System.out.println("dao层 获得可以选择加入黑名单的集合"+enterpriseMap);
        for (EnterVo enterprise : enterpriseList) {
            enterpriseMap.put(enterprise.getId(), enterprise.getEntername());//getEnterName
        }
        return enterpriseMap;
    }

    /*添加供应商到黑名单*/
    @Override
    public int insertBlackEnterByName(Integer enterid, Integer whiteid, String reason, LocalDate date) {
        String sql = "INSERT INTO supplierblacklist (id,enterid, supplierid, reason, updatedate)  " +
                " VALUES (null,?,?,?,?);";
        return db.dtUpdate(sql,enterid,whiteid,reason,date);

    }


    /*根据企业名称 获得 企业id  从白名单中查询 供应商id 然后根据两个id进行删除*/
    /*从黑名单中删除数据*/
    @Override
    public int delBlackEnter(Integer enterid, Integer whiteid) {
        String sql = "DELETE FROM supplierblacklist\n" +
                "WHERE enterid = ? AND supplierid = ?; ";
        return db.dtUpdate(sql,enterid,whiteid);
    }

}
